# src/export_service.py
import logging
import os
import uuid

from reportagentic.core.db import MetadataRepository, TemplateRepository, DatabaseConnector, QueryCacheRepository
from reportagentic.core.llm import LLMClient
from reportagentic.core.vector import VectorClient
from reportagentic.utils.report_generator import create_report_file_from_template

# 使用一个简单的字典作为内存缓存来存储预览和最终查询之间的状态
# 在生产环境中，应使用 Redis 或类似方案
PREVIEW_CACHE = {}


class ExportService:

    def __init__(self, vector_client: VectorClient,
                 llm_client: LLMClient,
                 meta_repo: MetadataRepository,
                 template_repo: TemplateRepository,
                 query_cache_repo: QueryCacheRepository,
                 source_db_connector: DatabaseConnector,
                 reports_dir: str):
        self.vector_client = vector_client
        self.llm_client = llm_client
        self.meta_repo = meta_repo
        self.template_repo = template_repo
        self.query_cache_repo = query_cache_repo
        self.source_db_connector = source_db_connector
        self.reports_dir = reports_dir
        os.makedirs(self.reports_dir, exist_ok=True)

    async def generate_preview(self, user_query: str) -> dict:
        """
        1. 查询语义缓存。
        2. 若未命中，则搜索模板向量。
        3. 使用预设的字段映射来约束并生成SQL。
        """
        query_vector = self.llm_client.get_embeddings([user_query])[0]
        final_sql = ""
        template_id = None
        template_path = ""

        # 1. 优先搜索语义缓存
        cache_results = self.vector_client.search("query_cache_vectors", query_vector, top_k=1)
        if cache_results and cache_results[0][1] > 0.8:
            cache_id, similarity = cache_results[0]
            logging.info(f"CACHE HIT: Found similar query in cache with ID {cache_id} (similarity: {similarity:.4f}).")
            cached_pair = self.query_cache_repo.get_by_id(cache_id)
            if cached_pair:
                final_sql = self.llm_client.adapt_sql_from_cache(user_query, cached_pair['generated_sql'])
                template_id = cached_pair['template_id']
                template_info = self.template_repo.get_template_by_id(template_id)
                if template_info:
                    template_path = template_info['example_file_path']

        # 2. 缓存未命中，执行新的模板搜索和映射流程
        if not final_sql:
            logging.info("CACHE MISS: No suitable cache found. Searching templates...")

            # 2a. 搜索最匹配的模板
            template_results = self.vector_client.search("template_vectors", query_vector, top_k=1)
            if not template_results:
                raise ValueError("抱歉，没有找到能够匹配您需求的报表模板。")

            template_id, similarity = template_results[0]
            logging.info(f"Found best match template: ID={template_id} with similarity={similarity:.4f}")
            template_info = self.template_repo.get_template_by_id(template_id)
            if not template_info:
                raise ValueError(f"数据不一致：找不到模板ID {template_id} 的记录。")
            template_path = template_info['example_file_path']

            # 2b. 获取该模板预设的字段映射
            mappings = self.template_repo.get_mapped_parameters(template_id)
            if not mappings:
                raise ValueError(f"模板(ID: {template_id})虽然被匹配到，但其并未配置字段映射关系，无法生成查询。")

            # 2c. 获取DB Schema并调用新的LLM方法生成SQL
            db_schema_str = self.meta_repo.get_schema_as_string()
            final_sql = self.llm_client.generate_sql_with_fixed_mappings(
                user_query=user_query,
                db_schema_string=db_schema_str,
                mappings=mappings,
            )

        # 3. 校验、预览、缓存
        if not final_sql or not final_sql.strip().upper().startswith("SELECT"):
            raise ValueError("系统无法生成有效的查询语句，请尝试换一种方式提问。")
        logging.info(f"Final Generated SQL: {final_sql}")

        preview_sql = f"{final_sql} LIMIT 10"
        headers, preview_data = self.source_db_connector.execute_query(preview_sql)

        # 预览缓存的Headers应基于查询结果，而不是模板文件，因为可能有别名
        final_headers = headers

        query_id = str(uuid.uuid4())
        PREVIEW_CACHE[query_id] = {
            "full_sql": final_sql,
            "headers": final_headers,
            "user_query": user_query,
            "template_id": template_id,
            "template_path": template_path
        }

        return {"query_id": query_id, "headers": final_headers, "preview_data": preview_data}


    async def download_full_report(self, query_id: str) -> str:
        """
        根据 query_id 执行完整查询并生成文件。
        """
        if query_id not in PREVIEW_CACHE:
            raise ValueError("无效或已过期的查询ID。")

        cached_data = PREVIEW_CACHE.pop(query_id)
        full_sql = cached_data['full_sql']
        headers = cached_data['headers']
        user_query = cached_data['user_query']
        template_id = cached_data['template_id']
        template_path = cached_data['template_path']

        # 1. 执行完整查询
        _, full_data = self.source_db_connector.execute_query(full_sql)
        logging.info(f"Fetched {len(full_data)} rows for full report.")

        # 2. 生成报表文件
        output_filename = f"report_{uuid.uuid4().hex[:8]}.xlsx"
        output_path = os.path.join(self.reports_dir, output_filename)
        create_report_file_from_template(
            base_template_path=template_path,
            output_path=output_path,
            headers=headers,
            data=full_data
        )

        try:
            logging.info(f"Writing successful query to semantic cache. NLQ: '{user_query}'")
            # 3. 保存文本对到数据库
            cache_id = self.query_cache_repo.save_query_pair(user_query, full_sql, template_id)

            # 4. 生成并存储向量
            text_to_embed = f"User Query: {user_query}\nSQL: {full_sql}"
            vector = self.llm_client.get_embeddings([text_to_embed])[0]

            self.vector_client.upsert_vectors(
                collection_name="query_cache_vectors",
                ids=[cache_id],
                vectors=[vector],
                payloads=[{"nlq": user_query}]
            )
            logging.info(f"Successfully cached query pair with ID {cache_id}.")
        except Exception as e:
            # 缓存失败不应影响主流程，只记录错误
            logging.error(f"Failed to write to semantic cache: {e}", exc_info=True)

        return output_path
